MySQL tabellen samenvoegen met JOIN

Probleem

Om de integriteit van de database te bewaren normaliseren we de gegevens in de database. Door de normalisering geraakt de informatie echter verspreid over meer dan één tabel. De gebruiker heeft daar echter niet veel aan. Primaire - en vreemde sleutels zeggen de gebruiker niets. We moeten dus een manier vinden om de informatie weer voor te stellen alsof die uit één tabel komt.

Oplossing

Een JOIN kunnen we gebruiken voor het combineren van gegevens uit twee of meer tabellen. Er zijn meerdere varianten van de JOIN.

1. INNER JOIN: wanneer we gebruik gaan maken van de INNER JOIN zullen de records uit beide tabellen worden gehaald welke aan elkaar zijn verbonden.
2. LEFT [OUTER] JOIN: met deze clausule kunnen we alle records ophalen uit de tabel die aan de linkerkant van de JOIN staat gespecificeerd, dus ook de rijen uit de linkse tabel die niet aan de join voorwaarde voldoen worden ook in het resultaat weergegeven.
3. RIGHT [OUTER] JOIN: met deze clausule kunnen we alle records ophalen uit de tabel die aan de rechterkant van de JOIN staat gespecificeerd, dus ook de rijen uit de rechtse tabel die niet aan de join voorwaarde voldoen worden ook in het resultaat weergegeven.
4. FULL [OUTER] JOIN: met deze clausule kunnen we alle records ophalen uit de tabel die zowel aan de linkerkant en aan de rechterkant van de JOIN staan gespecificeerd, dus ook de rijen uit de linkse en rechtse tabel die niet aan de join voorwaarde voldoen worden ook in het resultaat weergegeven.

Voorbeelden

Sla de oefeningen op in een bestand met de naam BoekenPersonenJoin.sql.

Inner join

Het diagram met de naam A staat voor de tabel Boeken en met de naam B voor Personen.

SELECT <select_list> 
   FROM Table_A
   INNER JOIN Table_B ON Table_A.Key = Table_B.Key

Selecteer alle boeken en toon de voornaam en de familienaam van de auteur. De 'master' tabel is in dat geval Boeken en de 'slave' tabel Personen.

select Personen.Voornaam, Personen.Familienaam,
      Boeken.Titel from Boeken
   inner join Personen on Boeken.IdAuteur = Personen.Id;

Voor elke rij uit de tabel Boeken wordt er opgezocht als er met de waarde die in de foreign key kolom IdAuteur van Boeken een waarde bestand in de primary key kolom Id van Personen. Als er een match in wordt de rij geselecteerd. Indien er geen match is wordt de rij in de tabel Boeken genegeerd.

We wijzigen de vraag lichtjes en willen nu alle auteurs zien en de boeken die ze geschreven hebben. We ordenen de lijst op Familienaam, Voornaam, Titel

select Personen.Voornaam, Personen.Familienaam,
      Boeken.Titel from Personen
   inner join Boeken on Boeken.IdAuteur = Personen.Id
   order by Personen.Voornaam, Personen.Familienaam, Boeken.Titel;

Dat resulteert in indentiek dezelfde lijst.

We voegen een nieuwe persoon toe in de tabel Personen:

insert into Personen (
   Voornaam, 
   Familienaam,
   AanspreekTitel,
   Straat, 
   Huisnummer,
   Stad, 
   Commentaar,
   Biografie
)
values
(
   'Simone', 
   'De Beauvoir', 
   'Mevrouw',
   'Rue Charles De Gaulle', 
   '38', 
   'Paris', 
   'Feministe',
   'Compagnon van Jean-Paul Sartre'
);

We selecteren alle personen geordend op Familienaam en Voornaam:

select * from Personen
order by Familienaam, Voornaam;

En we zien dat Simone De Beauvois is toegevoegd.

Alle auteurs en hun boeken te selecteren, maar ordenen de lijst nu op Familienaam, Voornaam en Titel:

select Personen.Voornaam, Personen.Familienaam,
      Boeken.Titel from Personen
   inner join Boeken on Boeken.IdAuteur = Personen.Id
   order by Personen.Familienaam, Personen.Voornaam, Boeken.Titel;

Nu merken we dat Simone De Beauvoir niet meer in de lijst voorkomt. Dat komt doordat er voor Simone de Beauvoir geen boeken in de boekentabel zijn opgenomen.

Een inner join gaat alleen die personen tonen waarvoor een match in de tabel Boeken wordt gevonden.

Left join

SELECT <select_list>
   FROM Table_A 
   LEFT JOIN Table_B 
   ON Table_A.Key = Table_B.Key

Als je alle personen wilt tonen ongeacht of ze een boek hebben geschreven of niet kan je een left join gebruiken. De 'linkse' tabel is de master tabel. Dus met een left join worden alle rijen uit de linkse tabel Personen geselecteerd ongeacht of ze een boek hebben geschreven of niet.

select Personen.Voornaam, Personen.Familienaam,
      Boeken.Titel from Personen
   left join Boeken on Boeken.IdAuteur = Personen.Id
   order by Personen.Familienaam, Personen.Voornaam, Boeken.Titel;

Simone is nu wel geselecteerd. Maar er is geen boek van haar aanwezig in de tabel Boeken, dus staat de Titel op null.

Coalesce

De functie COALESCE in SQL retourneert de eerste niet-NULL expressie tussen de argumenten.

Met de functie coalesce kan je aangeven wat erin een null kolom moet worden getoond:

select Personen.Voornaam, Personen.Familienaam,
   coalesce (Boeken.Titel, 'Geen boek opgenomen in Boeken') from Personen
   left join Boeken on Boeken.IdAuteur = Personen.Id
   order by Personen.Familienaam, Personen.Voornaam, Boeken.Titel;

Right join

SELECT <select_list>
   FROM Table_A
   RIGHT JOIN Table_B
   ON Table_A.Key = Table_B.Key

Als ik de tabellen in de select statement switch en Boeken links en Personen rechts zet, gaat Simone de Beauvoir getoond worden?

select Personen.Voornaam, Personen.Familienaam,
   coalesce (Boeken.Titel, 'Geen boek opgenomen in Boeken') from Boeken
   left join Personen on Boeken.IdAuteur = Personen.Id
   order by Personen.Familienaam, Personen.Voornaam, Boeken.Titel;

Geen Simone de Beauvoir te zien. Hoe kan ik Simone tonen?

Door een right join:

select Personen.Voornaam, Personen.Familienaam,
   coalesce (Boeken.Titel, 'Geen boek opgenomen in Boeken') from Boeken
   right join Personen on Boeken.IdAuteur = Personen.Id
   order by Personen.Familienaam, Personen.Voornaam, Boeken.Titel;

Simone De Beauvoir wordt getoond en de coalesce functie doet zijn werk:

Alles goed en wel maar de kolomkop voor de Titel trekt op niets. Je kan die zelf opgeven in het select statement door een alias mee te geven:

select Personen.Voornaam, Personen.Familienaam,
   coalesce (Boeken.Titel, 'Geen boek opgenomen in Boeken')
       as 'Titel van het boek'
   from Boeken
   right join Personen on Boeken.IdAuteur = Personen.Id
   order by Personen.Familienaam, Personen.Voornaam, Boeken.Titel;

Outer join

SELECT <select_list>
   FROM Table_A
   FULL OUTER JOIN Table_B
   ON Table_A.Key = Table_B.Key

Left excluding join

Deze query retourneert alle records in de linkertabel (tabel A) die niet overeenkomen met records in de rechtse tabel (tabel B). Deze join wordt als volgt geschreven:

SELECT <select_list>
   FROM Table_A
   LEFT JOIN Table_B
   ON Table_A.Key = Table_B.Key
   WHERE Table_B.Key IS NULL

Right excluding join

Deze query retourneert alle records in de rechtse tabel (tabel B) die niet overeenkomen met records in de linkertabel (tabel A). Deze join wordt als volgt geschreven:

SELECT <select_list>
   FROM Table_A 
   RIGHT JOIN Table_B 
   ON Table_A.Key = Table_B.Key
   WHERE Table_A.Key IS NULL

Outer excluding join

Deze query retourneert alle records in de linkertabel (tabel A) en alle records in de rechtertabel (tabel B) die niet overeenkomen. Deze join wordt als volgt geschreven:

SELECT <select_list>
   FROM Table_A 
   FULL OUTER JOIN Table_B 
   ON Table_A.Key = Table_B.Key
   WHERE Table_A.Key IS NULL OR Table_B.Key IS NULL